package com.axonactive.yolo.monitor;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.nio.channels.Channels;
import java.nio.channels.ReadableByteChannel;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;

import javax.annotation.Resource;
import javax.mail.MessagingException;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.thymeleaf.context.Context;

import com.axonactive.yolo.dto.Email;
import com.axonactive.yolo.service.interfaces.EmailService;

public class UpdateAirportData {

	public final String SQL_SERVER = "192.168.70.42:3306";
	public final String DB_NAME = "yoloflight";
	public final String AIRPORT_TABLE_COPY = "yoloflight.tbl_airport_copy";
	public final String AIRPORT_TABLE = "yoloflight.tbl_airport";
	public final String AIRPORT_TABLE_CITY_CODE = "yoloflight.tbl_airport.city_code";
	public final String AIRPORT_TABLE_COUNTRY_CODE = "yoloflight.tbl_airport.country_code";
	public final String AIRPORT_TABLE_STATE_CODE = "yoloflight.tbl_airport.state_code";
	public final String AIRPORT_TABLE_STATE_NAME = "yoloflight.tbl_airport.state_name";
	public final String STATE_TABLE = "yoloflight.tbl_airport_with_state";
	public final String STATE_TABLE_STATE_NAME = "yoloflight.tbl_airport_with_state.state_name";
	public final String STATE_TABLE_STATE_CODE = "yoloflight.tbl_airport_with_state.state_code";
	public final String STATE_TABLE_CITY_CODE = "yoloflight.tbl_airport_with_state.city_code";
	public final String STATE_TABLE_COUNTRY_CODE = "yoloflight.tbl_airport_with_state.country_code";
	public final String USER_NAME = "deployuser";
	public final String PASSWORD = "deployuser";

	public final Logger LOGGER = Logger.getLogger(UpdateAirportData.class);
	public final String URL_SOURCE = "http://support.wan.travel/hc/en-us/article_attachments/200168455/IATA_en.csv";
	public final int APP_LOCATION_TYPE_INDEX = 0;
	public final int AIRPORT_NAME_INDEX = 1;
	public final int AIRPORT_CODE_INDEX = 2;
	public final int CITY_NAME_INDEX = 3;
	public final int CITY_CODE_INDEX = 4;
	public final int COUNTRY_NAME_INDEX = 5;
	public final int COUNTRY_CODE_INDEX = 6;

	public Connection connect = null;
	public PreparedStatement preparedStatement = null;

	@Resource
	private Environment env;

	@Autowired
	public EmailService emailService;

	public final String PROPERTY_EMAIL_SUCCESS_UPDATE_AIRPORT_DATABASE = "propety.email.success.update.airport.database";
	public final String PROPERTY_EMAIL_ERROR_UPDATE_AIRPORT_DATABASE = "propety.email.error.update.airport.database";
	public final String EMAIL_SUCCESS_UPDATE_AIRPORT_DATABASE = "email.success.update.airport.database";
	public final String EMAIL_ERROR_UPDATE_AIRPORT_DATABASE = "email.error.update.airport.database";
	public final String YOLO_EMAIL = "yolo.email";
	public String FILE_PATH = "";

	public boolean updateDatabaseOfAirportCopy() {
		LOGGER.info("UpdateAirportData RUNNING");
		BufferedReader br = null;
		int lineNum = 0;

		try {
			Calendar cal = Calendar.getInstance();
			SimpleDateFormat dateFormat = new SimpleDateFormat(
					"yyyy-MM-dd hh-mm-ss");
			String dateStr = dateFormat.format(cal.getTime());
			FILE_PATH = System.getProperty("user.home") + File.separator
					+ "airport-" + dateStr + ".txt";

			URL website = new URL(URL_SOURCE);
			ReadableByteChannel rbc = Channels.newChannel(website.openStream());
			FileOutputStream fos = new FileOutputStream(FILE_PATH);
			fos.getChannel().transferFrom(rbc, 0, Long.MAX_VALUE);

			String sCurrentLine;

			br = new BufferedReader(new FileReader(FILE_PATH));

			Class.forName("com.mysql.jdbc.Driver");
			connect = DriverManager.getConnection("jdbc:mysql://" + SQL_SERVER
					+ "/" + DB_NAME + "?" + "user=" + USER_NAME + "&password="
					+ PASSWORD);

			// truncate db
			preparedStatement = connect.prepareStatement("TRUNCATE "
					+ AIRPORT_TABLE_COPY);
			preparedStatement.executeUpdate();
			LOGGER.info("UpdateAirportData Finish truncate copy table");

			String[] listTemp;
			String temp;

			while ((sCurrentLine = br.readLine()) != null) {
				// LOGGER.info(lineNum + " " + sCurrentLine);
				lineNum++;
				sCurrentLine = sCurrentLine.substring(1);
				sCurrentLine = sCurrentLine.substring(0,
						sCurrentLine.length() - 1);
				sCurrentLine = sCurrentLine.replace("L,N", "L;N");
				sCurrentLine = sCurrentLine.replace("\",\"", ";");
				sCurrentLine = sCurrentLine.replace("\",", ";");
				sCurrentLine = sCurrentLine.replace(",\"", ";");
				listTemp = sCurrentLine.split(";");

				// edit data for db
				if (listTemp[APP_LOCATION_TYPE_INDEX].equals("City")) {
					if (!listTemp[AIRPORT_NAME_INDEX].contains("City")) {
						listTemp[CITY_NAME_INDEX] = listTemp[AIRPORT_NAME_INDEX]
								+ " City";
					} else {
						listTemp[CITY_NAME_INDEX] = listTemp[AIRPORT_NAME_INDEX];
					}
					listTemp[CITY_CODE_INDEX] = listTemp[AIRPORT_CODE_INDEX];
					listTemp[AIRPORT_NAME_INDEX] = null;
					listTemp[AIRPORT_CODE_INDEX] = null;
				} else if (listTemp[APP_LOCATION_TYPE_INDEX].equals("Airport")) {
					temp = listTemp[AIRPORT_NAME_INDEX];

					if (temp.endsWith("Apt")) {
						temp = temp.substring(0, temp.indexOf("Apt"));
						temp += "Airport";
						listTemp[AIRPORT_NAME_INDEX] = temp;
					} else if (!temp.endsWith("Airport")) {
						temp += " Airport";
						listTemp[AIRPORT_NAME_INDEX] = temp;
					}
				}

				if (lineNum != 1) {

					preparedStatement = connect.prepareStatement("insert into "
							+ AIRPORT_TABLE_COPY
							+ " values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
					preparedStatement.setString(1, listTemp[0]);
					preparedStatement.setString(2, listTemp[1]);
					preparedStatement.setString(3, listTemp[2]);
					preparedStatement.setString(4, listTemp[3]);
					preparedStatement.setString(5, listTemp[4]);
					preparedStatement.setString(6, listTemp[5]);
					preparedStatement.setString(7, listTemp[6]);
					preparedStatement.setString(8, null);
					preparedStatement.setString(9, null);
					preparedStatement.setString(10, null);
					preparedStatement.executeUpdate();

				}

			}

			LOGGER.info("UpdateAirportData Finish inport data to copy table");

			// delete airport when city has one airport
			preparedStatement = connect
					.prepareStatement("SELECT * FROM yoloflight.tbl_airport_copy S"
							+ " WHERE S.app_location_type='Airport' AND (SELECT count(city_code)"
							+ " FROM yoloflight.tbl_airport_copy"
							+ " WHERE city_code=S.city_code) = 2");
			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				preparedStatement = connect
						.prepareStatement("DELETE FROM " + AIRPORT_TABLE_COPY
								+ " WHERE id = " + rs.getInt("id"));
				preparedStatement.executeUpdate();

			}

			LOGGER.info("UpdateAirportData Finish delete all airport which is only in one city");

			preparedStatement = connect.prepareStatement("TRUNCATE "
					+ AIRPORT_TABLE);
			preparedStatement.executeUpdate();
			LOGGER.info("UpdateAirportData Finish truncate original table");

			preparedStatement = connect.prepareStatement("INSERT INTO "
					+ AIRPORT_TABLE + " SELECT * FROM " + AIRPORT_TABLE_COPY);
			preparedStatement.executeUpdate();
			
			// update state to airport table
			preparedStatement = connect.prepareStatement("UPDATE "
					+ AIRPORT_TABLE + " INNER JOIN " + STATE_TABLE + " ON "
					+ STATE_TABLE_STATE_CODE + " IS NOT NULL " + " AND "
					+ STATE_TABLE_CITY_CODE + " = " + AIRPORT_TABLE_CITY_CODE
					+ " AND " + STATE_TABLE_COUNTRY_CODE + " = "
					+ AIRPORT_TABLE_COUNTRY_CODE + " SET "
					+ AIRPORT_TABLE_STATE_CODE + " = " + STATE_TABLE_STATE_CODE
					+ "," + AIRPORT_TABLE_STATE_NAME + " = "
					+ STATE_TABLE_STATE_NAME);
			preparedStatement.executeUpdate();
			LOGGER.info("UpdateAirportData Finish inport data to original table");

		} catch (ClassNotFoundException e) {
			LOGGER.info(e);
			return false;
		} catch (SQLException e) {
			LOGGER.info(e);
			return false;
		} catch (IOException e) {
			LOGGER.info(e);
			return false;
		} finally {
			try {
				if (br != null)
					br.close();
			} catch (IOException e) {
				LOGGER.info(e);
			}

			if (connect != null) {
				try {
					connect.close();
				} catch (SQLException e) {
					LOGGER.info(e);
				}
			}
		}

		return true;
	}

	public void run() {

		if (updateDatabaseOfAirportCopy() == true) {
			try {
				sendSuccessEmail();
			} catch (MessagingException e) {
				LOGGER.info(e);
			}
		} else {
			try {
				sendErrorEmail();
			} catch (MessagingException e) {
				LOGGER.info(e);
			}
		}
	}

	public void sendSuccessEmail() throws MessagingException {
		Email email = new Email();
		email.setSubject(env
				.getRequiredProperty(PROPERTY_EMAIL_SUCCESS_UPDATE_AIRPORT_DATABASE));
		email.setTo(env.getRequiredProperty(YOLO_EMAIL));
		email.setTemplate("update-airport-success-template");

		Context ctx = new Context();
		ctx.setVariable("email", env.getRequiredProperty(YOLO_EMAIL));
		ctx.setVariable("message",
				env.getRequiredProperty(EMAIL_SUCCESS_UPDATE_AIRPORT_DATABASE));

		email.setBody(ctx);
		emailService.sendEmail_(email);
	}

	public void sendErrorEmail() throws MessagingException {
		Email email = new Email();
		email.setSubject(env
				.getRequiredProperty(PROPERTY_EMAIL_ERROR_UPDATE_AIRPORT_DATABASE));
		email.setTo(env.getRequiredProperty(YOLO_EMAIL));
		email.setTemplate("update-airport-error-template");

		Context ctx = new Context();
		ctx.setVariable("email", env.getRequiredProperty(YOLO_EMAIL));
		ctx.setVariable("message",
				env.getRequiredProperty(EMAIL_ERROR_UPDATE_AIRPORT_DATABASE));
		ctx.setVariable("linkSource", URL_SOURCE);
		ctx.setVariable("filePath", FILE_PATH);
		ctx.setVariable("server", SQL_SERVER + "-username: " + USER_NAME
				+ "-password: " + PASSWORD + "-db name: " + DB_NAME);

		email.setBody(ctx);
		emailService.sendEmail_(email);
	}

}
